
using Microsoft.Win32;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;

using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Button = System.Windows.Controls.Button;
using RadioButton = System.Windows.Controls.RadioButton;
using UserControl = System.Windows.Controls.UserControl;

namespace AugustSql
{
    /// <summary>
    /// InquirePage.xaml 的交互逻辑
    /// </summary>
    public partial class InquirePage : UserControl
    {
        public class Constant
        {
            public static string Key = "Index";
        }

        public string Code => tbCode.Text.Trim();
        public string Header => ((TabItem)tabControl1.SelectedItem).Header.ToString();
        private int currentPage;
        private string TableName = "";
        public DataSet saveDs{get;private set;} = new DataSet();
        int _pageSize = 50;
        private static object newSql = null; 
        public string DefaultSavePath = "E:\\Data";
        public bool IsDefaultPath = true;
        public Dictionary<string, string> ParamUnit = new Dictionary<string, string>();
        public static object NewSql
        {
            get { return newSql; }
            set { newSql = value; }
        }


        public InquirePage()
        {
            InitializeComponent();
            Loaded += InquirePage_Loaded;
        }

        private void InquirePage_Loaded(object sender, RoutedEventArgs e)
        {
            cb_InquireItem.ItemsSource = InquireTableTexts;
            if (cb_InquireItem.Items.Count > 0)
                cb_InquireItem.SelectedIndex = 0;
        }
        public void ToCodeSerch()
        {
            
        }
        [TypeConverter(typeof(StringArrayTypeConvetor))]
        public string[] InquireTableNames
        {
            get { return (string[])GetValue(InquireTableNamesProperty); }
            set { SetValue(InquireTableNamesProperty, value); }
        }
        public static readonly DependencyProperty InquireTableNamesProperty =
            DependencyProperty.Register("InquireTableNames", typeof(string[]), typeof(InquirePage), new PropertyMetadata(new string[0]));


        [TypeConverter(typeof(StringArrayTypeConvetor))]
        public string[] InquireTableTexts      
        {
            get { return (string[])GetValue(InquireTableTextsProperty); }
            set { SetValue(InquireTableTextsProperty, value); }
        }
        public static readonly DependencyProperty InquireTableTextsProperty =
            DependencyProperty.Register("InquireTableTexts", typeof(string[]), typeof(InquirePage), new PropertyMetadata(new string[0]));

        [TypeConverter(typeof(StringArrayTypeConvetor))]
        public string[] InquireTableColumns
        {
            get { return (string[])GetValue(InquireTableColumnsProperty); }
            set { SetValue(InquireTableColumnsProperty, value); }
        }
        public static readonly DependencyProperty InquireTableColumnsProperty =
            DependencyProperty.Register("InquireTableColumns", typeof(string[]), typeof(InquirePage), new PropertyMetadata(new string[0]));


        public string InquireKeyTimeName
        {
            get { return (string)GetValue(InquireKeyTimeNameProperty); }
            set { SetValue(InquireKeyTimeNameProperty, value); }
        }
        public static readonly DependencyProperty InquireKeyTimeNameProperty =
            DependencyProperty.Register("InquireKeyTimeName", typeof(string), typeof(InquirePage), new PropertyMetadata("时间"));

        public string InquireKeyStringName
        {
            get { return (string)GetValue(InquireKeyStringNameProperty); }
            set { SetValue(InquireKeyStringNameProperty, value); }
        }
        public static readonly DependencyProperty InquireKeyStringNameProperty =
            DependencyProperty.Register("InquireKeyStringName", typeof(string), typeof(InquirePage), new PropertyMetadata("条码"));
        /// <summary>
        /// 查询
        /// </summary>
        public void Inquire()
        {
             this.Serch(btnInqu);
        }
        public void SetHeader()
        {
            SetHeader("最近");
        }
        public void SetHeader(string head)
        {
            ((TabItem)tabControl1.SelectedItem).Header = head;
            for (int i = 0; i < radioButtonStack.Children.Count; i++)
            {
                RadioButton radioButton = radioButtonStack.Children[i] as RadioButton;
                if (radioButton.Content.ToString() == "最近20天")
                {
                    radioButton.IsChecked = true;
                }
                else
                {
                    radioButton.IsChecked = false;
                }
            }
        }
        public bool HasOneDataAtLeast()
        {
            if (this.saveDs.Tables.Count > 0)
            {
                if (saveDs.Tables[0].Rows.Count > 0)
                {
                    return true;
                }
            }
            return false;
        }
        public DataTable GetCurrentDataTable()
        {
            if (this.saveDs.Tables.Count > 0)
            {
                return saveDs.Tables[0];
            }
            return new DataTable();
        }
        public bool IsValidTimeSerchRange()
        {
            string startTime = dtpStart.DisPlayValue;
            string endTime = dtpEnd.DisPlayValue;
            int va = Convert.ToDateTime(startTime).CompareTo(Convert.ToDateTime(endTime));
            if (va >= 0)
            {
                return false; 
            }
            return true;
        }
        public DataSet 获取最近三个月的数据()
        {
            string endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string startTime = DateTime.Now.AddMonths(-3).ToString("yyyy-MM-dd HH:mm:ss");
            int va = Convert.ToDateTime(startTime).CompareTo(Convert.ToDateTime(endTime));
            string selectColumnNames = "";
            if (InquireTableColumns.Length == 0)
            {
                selectColumnNames = "*";
            }
            else
            {
                for (int i = 0; i < InquireTableColumns.Length; i++)
                {
                    selectColumnNames += $"`{InquireTableColumns[i]}`";
                    if (i != InquireTableColumns.Length - 1)
                    {
                        selectColumnNames += ",";
                    }
                }

            }
            DataSet dataSet = new DataSet();
            //按照采集时间降序查询
            string sql = string.Format("SELECT {0} FROM `" + TableName + "` WHERE `" + InquireKeyTimeName + "` BETWEEN '{1}'AND '{2}'ORDER BY `" + InquireKeyTimeName + "` DESC", selectColumnNames, startTime, endTime);
            dataSet = ((dynamic)NewSql).SqlClient.GetDataByPagination(sql, _pageSize);
            for (int m = 0; m < dataSet.Tables.Count; m++)
            {
                for (int i = 0; i < dataSet.Tables[m].Columns.Count; i++)
                {
                    if (ParamUnit.ContainsKey(dataSet.Tables[m].Columns[i].ColumnName))
                    {
                        dataSet.Tables[m].Columns[i].ColumnName = dataSet.Tables[m].Columns[i].ColumnName + "(" + ParamUnit[dataSet.Tables[m].Columns[i].ColumnName] + ")";
                    }
                }
                if (dataSet.Tables[m].Columns.Contains(Constant.Key))
                    dataSet.Tables[m].Columns.Remove(Constant.Key);
            }
            return dataSet;
        }

        void Serch(object sender)
        {
            Button button = sender as Button;
            if (button != null)
            {
                button.IsEnabled = false;
                button.Content = "查询中...";
            }

            try
            {
                TableName = InquireTableNames[InquireTableTexts.ToList().IndexOf(cb_InquireItem.Text)];
                string sql = "";
                string selectRadioText = "";
                if (((TabItem)tabControl1.SelectedItem).Header == "最近")
                {
                    for (int i = 0; i < radioButtonStack.Children.Count; i++)
                    {
                        RadioButton radioButton = radioButtonStack.Children[i] as RadioButton;
                        if ((bool)radioButton.IsChecked)
                        {
                            selectRadioText = radioButton.Content.ToString();
                            break;
                        }
                    }

                    string startTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                    string endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

                    switch (selectRadioText)
                    {
                        case "当前班次":
                            string[] rets = DateTimeHelper.GetDataShifDetail();
                            startTime = rets[2];
                            break;
                        case "前一班次":
                            string[] rets2 = DateTimeHelper.GetForeDataShifDetail();
                            startTime = rets2[2];
                            endTime = rets2[3];
                            break;
                        case "最近5天":
                            startTime = DateTime.Now.AddDays(-5).ToString("yyyy-MM-dd HH:mm:ss");
                            break;
                        case "最近15天":
                            startTime = DateTime.Now.AddDays(-15).ToString("yyyy-MM-dd HH:mm:ss");
                            break;
                        case "最近20天":
                            startTime = DateTime.Now.AddDays(-20).ToString("yyyy-MM-dd HH:mm:ss");
                            break;
                        case "最近一个月":
                            startTime = DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd HH:mm:ss");
                            break;
                        case "最近三个月":
                            startTime = DateTime.Now.AddMonths(-3).ToString("yyyy-MM-dd HH:mm:ss");
                            break;
                    }
                    int va = Convert.ToDateTime(startTime).CompareTo(Convert.ToDateTime(endTime));
                    if (va >= 0)
                    {
                        MessageBox.Show("查询时间不合理！", "提示", MessageBoxButton.OKCancel, MessageBoxImage.Error);
                        return;
                    }

                    string selectColumnNames = "";
                    if (InquireTableColumns.Length == 0)
                    {
                        selectColumnNames = "*";
                    }
                    else
                    {
                        for (int i = 0; i < InquireTableColumns.Length; i++)
                        {
                            selectColumnNames += $"`{InquireTableColumns[i]}`";
                            if (i != InquireTableColumns.Length - 1)
                            {
                                selectColumnNames += ",";
                            }
                        }

                    }
                    //按照采集时间降序查询
                    sql = string.Format("SELECT {0} FROM `" + TableName + "` WHERE `" + InquireKeyTimeName + "` BETWEEN '{1}'AND '{2}'ORDER BY `" + InquireKeyTimeName + "` DESC", selectColumnNames, startTime, endTime);
                }
                else if (((TabItem)tabControl1.SelectedItem).Header == "时间")
                {
                    string startTime = dtpStart.DisPlayValue;
                    string endTime = dtpEnd.DisPlayValue;
                    int va = Convert.ToDateTime(startTime).CompareTo(Convert.ToDateTime(endTime));
                    if (va >= 0)
                    {
                        MessageBox.Show("查询时间不合理！", "提示", MessageBoxButton.OKCancel, MessageBoxImage.Error);
                        return;
                    }

                    string selectColumnNames = "";
                    if (InquireTableColumns.Length == 0)
                    {
                        selectColumnNames = "*";
                    }
                    else
                    {
                        for (int i = 0; i < InquireTableColumns.Length; i++)
                        {
                            selectColumnNames += $"`{InquireTableColumns[i]}`";
                            if (i != InquireTableColumns.Length - 1)
                            {
                                selectColumnNames += ",";
                            }
                        }

                    }
                    //按照采集时间降序查询
                    sql = string.Format("SELECT {0} FROM `" + TableName + "` WHERE `" + InquireKeyTimeName + "` BETWEEN '{1}'AND '{2}'ORDER BY `" + InquireKeyTimeName + "` DESC", selectColumnNames, startTime, endTime);
                }
                else if (((TabItem)tabControl1.SelectedItem).Header == "条码")
                {
                    //二维码查询
                    if (tbCode.Text == string.Empty)
                    {
                        MessageBox.Show("输入的条码不能为空！！", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
                        // GlobalValues.log_Helper.DispProcess("输入的条码不能为空", "Alarm", true);
                    }
                    else
                    {
                        sql = string.Format("SELECT * FROM `" + TableName + "` WHERE `" + InquireKeyStringName + "`LIKE'%{0}%'", tbCode.Text);
                    }
                }
                saveDs.Tables.Clear();
                saveDs.Clear();//每次查询，都会想saveDs数据集里写数据，会导致数据重复，所以需要在此处清空一次
                if (NewSql == null)
                {
                    MessageBox.Show("未配置数据库，请联系工程师！", "错误", MessageBoxButton.OKCancel, MessageBoxImage.Error);
                    return;
                }
                saveDs = ((dynamic)NewSql).SqlClient.GetDataByPagination(sql, _pageSize);
                for (int m = 0; m < saveDs.Tables.Count; m++)
                {
                    for (int i = 0; i < saveDs.Tables[m].Columns.Count; i++)
                    {
                        if (ParamUnit.ContainsKey(saveDs.Tables[m].Columns[i].ColumnName))
                        {
                            saveDs.Tables[m].Columns[i].ColumnName = saveDs.Tables[m].Columns[i].ColumnName + "(" + ParamUnit[saveDs.Tables[m].Columns[i].ColumnName] + ")";
                        }
                    }
                    if (saveDs.Tables[m].Columns.Contains(Constant.Key))
                        saveDs.Tables[m].Columns.Remove(Constant.Key);
                }
                if (saveDs.Tables.Count > 1)
                {
                    dgvProduct.ItemsSource = saveDs.Tables[1].DefaultView;
                    currentPage = 1;
                    lbPage.Text = string.Format("第{0}页/共{1}页", currentPage, saveDs.Tables.Count - 1);
                    tbTotalNum.Text = "共  " + saveDs.Tables[0].Rows.Count.ToString() + "  条数据";
                }
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.ToString(), "错误", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                if (button != null)
                {
                    button.IsEnabled = true;
                    button.Content = "查询";
                }
            }
        }
        private async void bt_Inquire(object sender, RoutedEventArgs e)
        {
            Button button = sender as Button;
            if (button != null)
            {
                button.IsEnabled = false;
                button.Content = "查询中...";
            }

            try
            {
                TableName = InquireTableNames[InquireTableTexts.ToList().IndexOf(cb_InquireItem.Text)];
                string sql = "";
                string selectRadioText = "";
                if (((TabItem)tabControl1.SelectedItem).Header == "最近")
                {
                    for (int i = 0; i < radioButtonStack.Children.Count; i++)
                    {
                        RadioButton radioButton = radioButtonStack.Children[i] as RadioButton;
                        if ((bool)radioButton.IsChecked)
                        {
                            selectRadioText = radioButton.Content.ToString();
                            break;
                        }
                    }

                    string startTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                    string endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

                    switch (selectRadioText)
                    {
                        case "当前班次":
                            string[] rets = DateTimeHelper.GetDataShifDetail();
                            startTime = rets[2];
                            break;
                        case "前一班次":
                            string[] rets2 = DateTimeHelper.GetForeDataShifDetail();
                            startTime = rets2[2];
                            endTime = rets2[3];
                            break;
                        case "最近5天":
                            startTime = DateTime.Now.AddDays(-5).ToString("yyyy-MM-dd HH:mm:ss");
                            break;
                        case "最近15天":
                            startTime = DateTime.Now.AddDays(-15).ToString("yyyy-MM-dd HH:mm:ss");
                            break;
                        case "最近20天":
                            startTime = DateTime.Now.AddDays(-20).ToString("yyyy-MM-dd HH:mm:ss");
                            break;
                        case "最近一个月":
                            startTime = DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd HH:mm:ss");
                            break;
                        case "最近三个月":
                            startTime = DateTime.Now.AddMonths(-3).ToString("yyyy-MM-dd HH:mm:ss");
                            break;
                    }
                    int va = Convert.ToDateTime(startTime).CompareTo(Convert.ToDateTime(endTime));
                    if (va >= 0)
                    {
                        MessageBox.Show("查询时间不合理！", "提示", MessageBoxButton.OKCancel, MessageBoxImage.Error);
                        return;
                    }

                    string selectColumnNames = "";
                    if (InquireTableColumns.Length == 0)
                    {
                        selectColumnNames = "*";
                    }
                    else
                    {
                        for (int i = 0; i < InquireTableColumns.Length; i++)
                        {
                            selectColumnNames += $"`{InquireTableColumns[i]}`";
                            if (i != InquireTableColumns.Length - 1)
                            {
                                selectColumnNames += ",";
                            }
                        }

                    }
                    //按照采集时间降序查询
                    sql = string.Format("SELECT {0} FROM `" + TableName + "` WHERE `" + InquireKeyTimeName + "` BETWEEN '{1}'AND '{2}'ORDER BY `" + InquireKeyTimeName + "` DESC", selectColumnNames, startTime, endTime);
                }
                else if (((TabItem)tabControl1.SelectedItem).Header.ToString() == "时间")
                {
                    string startTime = dtpStart.DisPlayValue;
                    string endTime = dtpEnd.DisPlayValue;
                    int va = Convert.ToDateTime(startTime).CompareTo(Convert.ToDateTime(endTime));
                    if (va >= 0)
                    {
                        MessageBox.Show("查询时间不合理！", "提示", MessageBoxButton.OKCancel, MessageBoxImage.Error);
                        return;
                    }

                    string selectColumnNames = "";
                    if (InquireTableColumns.Length == 0)
                    {
                        selectColumnNames = "*";
                    }
                    else
                    {
                        for (int i = 0; i < InquireTableColumns.Length; i++)
                        {
                            selectColumnNames += $"`{InquireTableColumns[i]}`";
                            if (i != InquireTableColumns.Length - 1)
                            {
                                selectColumnNames += ",";
                            }
                        }

                    }
                    //按照采集时间降序查询
                    sql = string.Format("SELECT {0} FROM `" + TableName + "` WHERE `" + InquireKeyTimeName + "` BETWEEN '{1}'AND '{2}'ORDER BY `" + InquireKeyTimeName + "` DESC", selectColumnNames, startTime, endTime);
                }
                else if (((TabItem)tabControl1.SelectedItem).Header.ToString() == "条码")
                {
                    //二维码查询
                    if (tbCode.Text == string.Empty)
                    {
                        MessageBox.Show("输入的条码不能为空！！", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
                        // GlobalValues.log_Helper.DispProcess("输入的条码不能为空", "Alarm", true);
                    }
                    else
                    {
                        sql = string.Format("SELECT * FROM `" + TableName + "` WHERE `" + InquireKeyStringName + "`LIKE'%{0}%'", tbCode.Text);
                    }
                }
                saveDs.Tables.Clear();
                saveDs.Clear();//每次查询，都会想saveDs数据集里写数据，会导致数据重复，所以需要在此处清空一次
                if (NewSql == null)
                {
                    MessageBox.Show("未配置数据库，请联系工程师！", "错误", MessageBoxButton.OKCancel, MessageBoxImage.Error);
                    return;
                }
                await Task.Run(() =>
                {
                    saveDs = ((dynamic)NewSql).SqlClient.GetDataByPagination(sql, _pageSize);
                });
                for (int m = 0; m < saveDs.Tables.Count; m++)
                {
                    for (int i = 0; i < saveDs.Tables[m].Columns.Count; i++)
                    {
                        if (ParamUnit.ContainsKey(saveDs.Tables[m].Columns[i].ColumnName))
                        {
                            saveDs.Tables[m].Columns[i].ColumnName = saveDs.Tables[m].Columns[i].ColumnName + "(" + ParamUnit[saveDs.Tables[m].Columns[i].ColumnName] + ")";
                        }
                    }
                    if (saveDs.Tables[m].Columns.Contains(Constant.Key))
                        saveDs.Tables[m].Columns.Remove(Constant.Key);
                }
                if (saveDs.Tables.Count > 1)
                {
                    dgvProduct.ItemsSource = saveDs.Tables[1].DefaultView;
                    currentPage = 1;
                    lbPage.Text = string.Format("第{0}页/共{1}页", currentPage, saveDs.Tables.Count - 1);
                    tbTotalNum.Text = "共  " + saveDs.Tables[0].Rows.Count.ToString() + "  条数据";
                }
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.ToString(), "错误", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                if (button != null)
                {
                    button.IsEnabled = true;
                    button.Content = "查询";
                }
            }
        }

        private void bt_Import(object sender, RoutedEventArgs e)
        {
            if (!Directory.Exists(DefaultSavePath))//判断文件夹是否存在
            {
                //不存在，创建文件夹
                Directory.CreateDirectory(DefaultSavePath);
            }
            string path = DefaultSavePath;
            if (IsDefaultPath)
            {
                path = DefaultSavePath + "\\" + DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx";
            }
            else
            {
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.DefaultExt = ".xlsx";
                saveFileDialog.Filter = "EXCEL|*.xlsx";
                bool? dialogResult = saveFileDialog.ShowDialog();
                if (dialogResult == true)
                {
                    path = saveFileDialog.FileName;
                }
                else
                {
                    return;
                }
            }

            Task.Factory.StartNew(t =>
            {
                string tt = t.ToString();
                try
                {
                    ExcelHelper.DataTableSaveAsExcel(saveDs.Tables[0], tt);
                    this.Dispatcher.Invoke(new Action(() =>
                    {
                        MessageBox.Show("数据导出成功！", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
                    }));
                    //打开数据文件夹
                    string path2 = System.IO.Path.GetDirectoryName(tt);
                    Process.Start("Explorer.exe", "/select," + tt);
                }
                catch (Exception ex)
                {
                    this.Dispatcher.Invoke(new Action(() =>
                    {
                        MessageBox.Show(ex.Message, "错误", MessageBoxButton.OK, MessageBoxImage.Error);
                    }));

                }
                finally
                {

                }
            }, path);
        }


        private void bt_FirstPage(object sender, RoutedEventArgs e)
        {
            if (saveDs.Tables.Count == 0)
            {
                return;
            }
            currentPage = 1;
            lbPage.Text = string.Format("第{0}页/共{1}页", currentPage, saveDs.Tables.Count - 1);
            dgvProduct.ItemsSource = saveDs.Tables[currentPage].DefaultView;
        }

        private void bt_UpPage(object sender, RoutedEventArgs e)
        {
            if (saveDs.Tables.Count == 0)
            {
                return;
            }
            if (currentPage == 1)
            {
                MessageBox.Show("已是第一页！", "提示", MessageBoxButton.OK, MessageBoxImage.Warning);
                return;
            }
            currentPage--;
            lbPage.Text = string.Format("第{0}页/共{1}页", currentPage, saveDs.Tables.Count - 1);
            dgvProduct.ItemsSource = saveDs.Tables[currentPage].DefaultView;
        }

        private void bt_DownPage(object sender, RoutedEventArgs e)
        {
            if (saveDs.Tables.Count == 0)
            {
                return;
            }
            if (currentPage == saveDs.Tables.Count - 1)
            {
                MessageBox.Show("已是最后一页！", "提示", MessageBoxButton.OK, MessageBoxImage.Warning);
                return;
            }
            currentPage++;
            lbPage.Text = string.Format("第{0}页/共{1}页", currentPage, saveDs.Tables.Count - 1);
            dgvProduct.ItemsSource = saveDs.Tables[currentPage].DefaultView;
        }

        private void bt_LastPage(object sender, RoutedEventArgs e)
        {
            if (saveDs.Tables.Count == 0)
            {
                return;
            }
            currentPage = saveDs.Tables.Count - 1;
            lbPage.Text = string.Format("第{0}页/共{1}页", currentPage, saveDs.Tables.Count - 1);
            dgvProduct.ItemsSource = saveDs.Tables[currentPage].DefaultView;
        }
        public event Action<string> TableItemChanged;
        private void cb_InquireItem_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            System.Windows.Controls.ComboBox cb = sender as System.Windows.Controls.ComboBox;
            TableItemChanged?.Invoke(cb.SelectedItem.ToString());
        }
    }
}
